<?php
/**
 * User: songlin
 * Date: 2017/10/17
 * 使用PHPExcel处理excel文件
 */

ini_set("memory_limit", "1024M"); // 设置php可使用内存
set_time_limit(0);  // 设置执行时间最大值

define('EOL', '<br />');
$fileUrl = 'data/test-2017-10-19-17-07-55.xlsx';
$rowSize = 10000;
$startRow = 2;//从第二行开始读取
$endRow = $rowSize;
$count = 0;
$excel_orders = array();
$size = filesize($fileUrl);
echo date('H:i:s'), ' 文件大小', getsize($size, 'mb'), EOL;

$callStartTime = microtime(true);

while (true) {
    $excel_orders = readFromExcel($fileUrl, $startRow, $endRow);
    if (empty($excel_orders)) {
        break;
    }
    $startRow = $endRow + 1;
    $endRow = $endRow + $rowSize;
    $count += count($excel_orders);
}
unset($excel_orders);

$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;

echo date('H:i:s'), ' 读取', $count, "条数据", EOL;
echo date('H:i:s'), ' 读取耗时 ', sprintf('%.4f', $callTime), " 秒", EOL;
echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
echo date('H:i:s'), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;

/**
 * 分页读取excel转换成数组
 * 大文件块级别读取 减少占用资源
 * @param string $excelFile 文件路径
 * @param int $startRow 开始读取的行数
 * @param int $endRow 结束读取的行数
 * @return array
 */
function readFromExcel($excelFile, $startRow = 1, $endRow = 100)
{
    include_once 'PHPExcel/PHPExcel.php';
    include_once 'PHPExcel/PHPExcelReadFilter.php';

    $excelType = PHPExcel_IOFactory::identify($excelFile);
    $excelReader = \PHPExcel_IOFactory::createReader($excelType);

    if (strtoupper($excelType) == 'CSV') {
        $excelReader->setInputEncoding('GBK');
    }

    if ($startRow && $endRow) {
        $excelFilter = new PHPExcelReadFilter();
        $excelFilter->startRow = $startRow;
        $excelFilter->endRow = $endRow;
        $excelReader->setReadFilter($excelFilter);
    }

    $phpexcel = $excelReader->load($excelFile);

//    $activeSheet = $phpexcel->getSheet($num); //设置工作页
    $activeSheet = $phpexcel->getActiveSheet();

    $highestColumn = $activeSheet->getHighestColumn(); //最后列数所对应的字母，例如第1行就是A
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //总列数

    $data = array();
    for ($row = $startRow; $row <= $endRow; $row++) {
        for ($col = 0; $col < $highestColumnIndex; $col++) {
            $data[$row][] = (string)$activeSheet->getCellByColumnAndRow($col, $row)->getValue();
        }
        if (implode($data[$row], '') == '') {
            unset($data[$row]);
        }
    }
    return $data;
}

/**
 * 读取数据量小，指定excel工作页
 * 无格式，这里不能转化时间戳 但是速度贼快
 * @param $object
 * @param int $num 指定工作表位置
 * @return int
 */
function simpleExcel($object, $num = 0)
{
    $count = 0;//执行条数

    $objWorksheet = $object->getSheet($num);//读取指定excel页
    $data = $objWorksheet->toArray(null, true, true, true);//转换数组

    foreach ($data as $key => $value) {
        $count++;
        $sheetData[$key][] = $value;
    }
    unset($sheetData);
    return $count;
}

/**
 * 读取指定规则内容，带格式的excel
 * 中庸
 * @param $object
 * @param $ruleList $ruleList = array("name1"=>array("A", "B", "C", "D", "F"), "name2"=>array("A", "B", "C", "D", "F"))
 * @return int
 */
function designExcel($object, $ruleList = array())
{
    $count = 0;

    //默认所有页
    if (empty($ruleList)) {
        $sheetCount = $object->getSheetCount();// excel页数
        $sheetNameList = $object->getSheetNames();// excel页名字数组

        //制定默认规则
        for ($i = 0; $i < $sheetCount; $i++) {
            $currentSheet = $object->getSheet($i);// 当前页
            $colNum = $currentSheet->getHighestColumn();// 当前页列数
            $colList[$i] = array();//页眉结构数组
            for ($column = 'A'; $column <= $colNum; $column++) {//列数是以A列开始
                array_push($colList[$i], $column);
            }
            $ruleList[$sheetNameList[$i]] = $colList[$i];
        }
    }

    // 循环页
    foreach ($ruleList as $key => $val) {
        $currentSheet = $object->getSheetByName($key);// 通过页名称取得当前页
        if (!empty($currentSheet)) {
            $rowNum = $currentSheet->getHighestRow();// 当前页行数

            // 循环从第二行开始，第一行往往是表头
            for ($i = 2; $i <= $rowNum; $i++) {
                foreach ($val as $col_val) {
                    $address = $col_val . $i;// 单元格坐标

                    // 读取单元格内容
                    $sheetData[$key][$i - 2][$col_val] = $currentSheet->getCell($address)->getFormattedValue();
                }
                $count++;
            }
        } else {
            $sheetData[$key] = '不存在该工作页！';
        }
    }
    unset($sheetData);
    return $count;
}

function getsize($size, $format = 'kb')
{
    $p = 0;
    if ($format == 'kb') {
        $p = 1;
    } elseif ($format == 'mb') {
        $p = 2;
    } elseif ($format == 'gb') {
        $p = 3;
    }
    $size /= pow(1024, $p);
    return number_format($size, 3);
}
